package com.sljr.mvndemo;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Properties;

public class DbUtil {
	private static String driver;
	private static String url;
	private static String username;
	private static String password;
	static {
		try {
			InputStream in = DbUtil.class.getClassLoader().getResourceAsStream(
					"jdbc.properties");
			Properties prop = new Properties();
			prop.load(in);
			in.close();

			driver = prop.getProperty("jdbc.driver");
			url = prop.getProperty("jdbc.url");
			username = prop.getProperty("jdbc.username");
			password = prop.getProperty("jdbc.password");

			Class.forName(driver);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private DbUtil() {

	}

	public static List<Map<String, Object>> execute(String sql) {
		List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();

		try {
			Connection connection = DriverManager.getConnection(url, username,
					password);

			Statement statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery(sql);
			ResultSetMetaData metaData = resultSet.getMetaData();
			int columnCount = metaData.getColumnCount();
			while (resultSet.next()) {
				Map<String, Object> row = new HashMap<String, Object>();
				for (int i = 0; i < columnCount; i++) {
					String columnName = metaData.getColumnName(i + 1);
					Object object = resultSet.getObject(i + 1);
					row.put(columnName, object);
				}
				result.add(row);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return result;
	}

	public static String getDriver() {
		return driver;
	}

	public static void setDriver(String driver) {
		DbUtil.driver = driver;
	}

	public static String getUrl() {
		return url;
	}

	public static void setUrl(String url) {
		DbUtil.url = url;
	}

	public static String getUsername() {
		return username;
	}

	public static void setUsername(String username) {
		DbUtil.username = username;
	}

	public static String getPassword() {
		return password;
	}

	public static void setPassword(String password) {
		DbUtil.password = password;
	}

	public static void main(String[] args) { String sql_oracle = "select t.table_name, tc.column_name, tc.data_type, tc.data_length from user_tables t, user_tab_columns tc where t.table_name = tc.table_name and t.table_name = 'COM_T_USER'";
		String sql_postgresql = "select tab.relname table_name, col.attname column_name,t.typname data_type, col.attlen data_length from pg_statio_user_tables tab, pg_attribute col, pg_type t where col.attrelid = tab.relid and t.oid = col.atttypid and tab.relname = 'address'";
		
		List<Map<String, Object>> list = DbUtil.execute(sql_postgresql);
		for (Map<String, Object> map : list) {
			System.out.println(map.get("TABLE_NAME"));
			for (Entry<String, Object> entry : map.entrySet()) {
				System.out.print(entry.getKey() + "-->" + entry.getValue()
						+ "\t");
			}
			System.out.println();
		}

	}

}
